package cn.com.lms.business.report.service;

import cn.com.lms.business.exam.enums.SubjectTypeEnum;
import cn.com.lms.business.learning.persistence.entity.FbLearningStageDetail;
import cn.com.lms.business.learning.persistence.repository.LearningStageDetailRepository;
import cn.com.lms.business.report.web.vo.CourseLearnReportVo;

import cn.com.lms.business.report.web.vo.CourseQuestionnaireReportVO;
import cn.com.lms.business.report.web.vo.QuestionnaireSubjectScoreVO;
import com.bnzj.core.export.Column;


import com.bnzj.core.persistence.BaseService;
import com.bnzj.core.persistence.query.QueryHelpBean;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.hibernate.internal.util.StringHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.util.Arrays;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;

@Service
public class CourseLearnReportService extends BaseService<LearningStageDetailRepository, FbLearningStageDetail,Long> {

    private final static Logger logger = LoggerFactory.getLogger(CourseLearnReportService.class);
    public static QueryHelpBean getQueryHelpBean() {
//        String selectSQL = "select DISTINCT(c.id) ,c.title,c.course_type,c.creator_name,c.create_time,c.credit," +
//                " (select count(1) from bnz_learning_student s where s.stage_detail_id=sd.id) count," +
//                " (select count(1) from bnz_learning_student s where s.stage_detail_id=sd.id and s.finish_state=1) finish_count, '' score" ;
//        String whereSQL = " from bnz_learning_stage_detail sd LEFT JOIN bnz_course_info c on sd.obj_id=c.id  where sd.study_type=0 ";

        String selectSQL="select  c.id,c.title,c.course_type,c.creator_name,c.create_time,c.credit, d.`name` AS course_type_name," +
                " (select count(1) from bnz_learning_student s left join bnz_learning_stage_detail sd on  s.stage_detail_id=sd.id where sd.obj_id=c.id and  sd.study_type=0) count, " +
                " (select count(1) from bnz_learning_student s left join bnz_learning_stage_detail sd on s.stage_detail_id=sd.id where sd.obj_id=c.id and  sd.study_type=0 and s.finish_state=1) finish_count " +
                ", '' score ";
        String whereSQL=" from bnz_course_info c  left join bnz_sys_dictionary d on c.course_type=d.id  where c.id in (" +
                " select  DISTINCT(ci.id) from bnz_learning_stage_detail sd  LEFT JOIN bnz_course_info ci on sd.obj_id=ci.id   where sd.study_type=0 ) ";
        QueryHelpBean queryBean = new QueryHelpBean(selectSQL + whereSQL, "select count(1)" + whereSQL, CourseLearnReportVo.class);
        return queryBean;
    }


    public HSSFWorkbook exportCourseQuestionnaireReport(CourseQuestionnaireReportVO courseQuestionnaireReportVO,String password) {
            String questionnailName=courseQuestionnaireReportVO.getQuestionnaireName()+"-评估报告";
        List<String> firstRowFildList = Arrays.asList("评估项目", "平均分", "项目","单项平均分");
        List<String> sheetNameList = Arrays.asList("课程评估报告");
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        sheetNameList.stream().forEach(sheetName -> {
            List<Column> columnList = new CopyOnWriteArrayList<>();


                firstRowFildList.stream().forEach(str -> {
                    Column column = new Column(str, str, firstRowFildList.indexOf(str));
                    columnList.add(column);
                });
                this.exportExcel(hssfWorkbook, questionnailName, sheetName, courseQuestionnaireReportVO, columnList,password);




        });


        return hssfWorkbook;

    }

    /**
     * 用于多Sheet导出，只插入数据，不保存文件
     *
     * @param workbook
     * @param title    sheetName
     * @param courseQuestionnaireReportVO
     * @param columns
     */
    public void exportExcel(HSSFWorkbook workbook, String questionnailName, String title, CourseQuestionnaireReportVO courseQuestionnaireReportVO, List<Column> columns,String password) {
        try {
            // 声明一个工作薄
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet(title);
            //设置单元格只读
            if (!StringUtils.isEmpty(password)) {
                sheet.protectSheet(password);
            }
            // 设置密码保护表格不可编辑
            // sheet.protectSheet("12345678");
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            // 生成一个样式
            HSSFCellStyle columnStyle = workbook.createCellStyle();
            // 生成一个字体
            HSSFFont columnFont = workbook.createFont();

            columnFont.setFontHeightInPoints((short) 11);
            columnFont.setBold(false);
            // 把字体应用到当前的样式 206,255,206
//            HSSFPalette customPalette = workbook.getCustomPalette();
//            HSSFColor hssfColor=customPalette.addColor((byte)206,(byte)255,(byte)206);
            columnStyle.setFont(columnFont);

            columnStyle.setFillBackgroundColor(IndexedColors.LIME.getIndex());
            columnStyle.setFillPattern(FillPatternType.NO_FILL);
            columnStyle.setAlignment(HorizontalAlignment.CENTER);
            columnStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 生成并设置另一个样式
            HSSFCellStyle dataStyle = workbook.createCellStyle();
            // 生成另一个字体
            HSSFFont dataFont = workbook.createFont();
            dataFont.setBold(false);
            // 把字体应用到当前的样式
            dataStyle.setFont(dataFont);


            HSSFFont font3 = workbook.createFont();
            // 声明一个画图的顶级管理器
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            // 产生表格填写须知
            // 生成一个样式
            HSSFCellStyle noticeStyle = workbook.createCellStyle();
            // 生成一个字体
            HSSFFont noticeFont = workbook.createFont();
            noticeFont.setFontHeightInPoints((short) 12);
            noticeFont.setBold(false);
            noticeFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            // 把字体应用到当前的样式
            noticeStyle.setFont(noticeFont);
            HSSFRow noticeRow = sheet.createRow(0);
            HSSFCell noticeCell = noticeRow.createCell(0);
            noticeStyle.setWrapText(true);
            noticeStyle.setAlignment(HorizontalAlignment.CENTER);
            noticeStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            noticeCell.setCellStyle(noticeStyle);
            HSSFRichTextString noticetext = new HSSFRichTextString(questionnailName);
            noticeCell.setCellValue(noticetext);
            noticeRow.setHeightInPoints(20);
            // 合并单元格
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

            List<QuestionnaireSubjectScoreVO> firstPartScoreList=courseQuestionnaireReportVO.getFirstPartScoreList();
            List<QuestionnaireSubjectScoreVO> secondPartScoreList=courseQuestionnaireReportVO.getSecondPartScoreList();
            List<QuestionnaireSubjectScoreVO> thirdPartScoreList=courseQuestionnaireReportVO.getThirdPartScoreList();

           String firstPartName= StringHelper.isNotEmpty(courseQuestionnaireReportVO.getFirstPartName())?courseQuestionnaireReportVO.getFirstPartName():"整体安排";
           String secondPartName= StringHelper.isNotEmpty(courseQuestionnaireReportVO.getSecondPartName())?courseQuestionnaireReportVO.getSecondPartName():"课程部分";
           String thirdPartName= StringHelper.isNotEmpty(courseQuestionnaireReportVO.getThirdPartName())?courseQuestionnaireReportVO.getThirdPartName():"讲师部分";

           String firstPartScore=courseQuestionnaireReportVO.getFirstPartAvgScore()!=null?String.format("%.2f",courseQuestionnaireReportVO.getFirstPartAvgScore()):"";
           String secondPartScore=courseQuestionnaireReportVO.getSecondPartAvgScore()!=null?String.format("%.2f",courseQuestionnaireReportVO.getSecondPartAvgScore()):"";
           String thirdPartScore=courseQuestionnaireReportVO.getThirdPartAvgScore()!=null?String.format("%.2f",courseQuestionnaireReportVO.getThirdPartAvgScore()):"";
           String totalPartScore=courseQuestionnaireReportVO.getTotalAvgScore()!=null?String.format("%.2f",courseQuestionnaireReportVO.getTotalAvgScore()):"";
           Integer totalSubjectCount=firstPartScoreList.size()+secondPartScoreList.size()+thirdPartScoreList.size();
           String singleAvgScore="";
           if(totalSubjectCount!=0 && courseQuestionnaireReportVO.getTotalAvgScore()!=null){
               singleAvgScore=String.format("%.2f",courseQuestionnaireReportVO.getSingleTotalAvgScore()/totalSubjectCount);
           }


            HSSFRow firstPartRow = sheet.createRow(2);
            HSSFCell firstPartCell = firstPartRow.createCell(0);
            HSSFCell firstPartScoreCell = firstPartRow.createCell(1);
            firstPartCell.setCellStyle(noticeStyle);
            firstPartScoreCell.setCellStyle(noticeStyle);
            HSSFRichTextString firstPartText = new HSSFRichTextString(firstPartName);
            HSSFRichTextString firstPartScoreCellText = new HSSFRichTextString(firstPartScore);
            firstPartCell.setCellValue(firstPartText);
            firstPartScoreCell.setCellValue(firstPartScoreCellText);


            HSSFRow secondPartRow = sheet.createRow(firstPartScoreList.size()==0?3:firstPartScoreList.size()+2);
            HSSFCell secondPartCell = secondPartRow.createCell(0);
            HSSFCell secondPartScoreCell = secondPartRow.createCell(1);
            secondPartCell.setCellStyle(noticeStyle);
            secondPartScoreCell.setCellStyle(noticeStyle);
            HSSFRichTextString secondPartText = new HSSFRichTextString(secondPartName);
            HSSFRichTextString secondPartScoreCellText = new HSSFRichTextString(secondPartScore);
            secondPartCell.setCellValue(secondPartText);
            secondPartScoreCell.setCellValue(secondPartScoreCellText);


            HSSFRow thirdPartRow = sheet.createRow(secondPartScoreList.size()+firstPartScoreList.size()+2);
            HSSFCell thirdPartCell = thirdPartRow.createCell(0);
            HSSFCell thirdPartScoreCell = thirdPartRow.createCell(1);
            thirdPartCell.setCellStyle(noticeStyle);
            thirdPartScoreCell.setCellStyle(noticeStyle);
            HSSFRichTextString thirdPartText = new HSSFRichTextString(thirdPartName);
            HSSFRichTextString thirdPartScoreCellText = new HSSFRichTextString(thirdPartScore);
            thirdPartCell.setCellValue(thirdPartText);
            thirdPartScoreCell.setCellValue(thirdPartScoreCellText);

            HSSFRow totalScoreRow = sheet.createRow(thirdPartScoreList.size()+secondPartScoreList.size()+firstPartScoreList.size()+2);
            HSSFCell totalPartCell = totalScoreRow.createCell(0);
            HSSFCell totalScoreCell = totalScoreRow.createCell(1);
            HSSFCell singleAvgScoreCell = totalScoreRow.createCell(3);
            totalPartCell.setCellStyle(noticeStyle);
            totalScoreCell.setCellStyle(noticeStyle);
            singleAvgScoreCell.setCellStyle(noticeStyle);
            HSSFRichTextString totalPartText = new HSSFRichTextString("总平均分");
            HSSFRichTextString totalPartScoreCellText = new HSSFRichTextString(totalPartScore);
            HSSFRichTextString singleAvgScoreCellText = new HSSFRichTextString(singleAvgScore);
            totalPartCell.setCellValue(totalPartText);
            totalScoreCell.setCellValue(totalPartScoreCellText);
            singleAvgScoreCell.setCellValue(singleAvgScoreCellText);


            // 产生表格标题行
            HSSFRow row = sheet.createRow(1);
            for (int i = 0; i < columns.size(); i++) {
                Column column = columns.get(i);
                if (column.isVisible()) {
                    sheet.setColumnWidth(i, 100 * column.getWidth());
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(columnStyle);
                    HSSFRichTextString text = new HSSFRichTextString(column.getTitle());
                    cell.setCellValue(text);
                }
            }


            if(!CollectionUtils.isEmpty(firstPartScoreList)) {
                firstPartScoreList.stream().forEach(questionnaireSubjectScoreVO->{
                    int index=firstPartScoreList.indexOf(questionnaireSubjectScoreVO);
                    HSSFRow questionnaireSubjectScoreVORow = sheet.getRow(2+index)==null?sheet.createRow(2+index):sheet.getRow(2+index);
                    HSSFCell subjectCell = questionnaireSubjectScoreVORow.createCell(2);
                    HSSFCell scoreCell = questionnaireSubjectScoreVORow.createCell(3);
                    subjectCell.setCellStyle(noticeStyle);
                    scoreCell.setCellStyle(noticeStyle);
                    HSSFRichTextString subjectText = new HSSFRichTextString(questionnaireSubjectScoreVO.getQuestionnaireTop());
                    HSSFRichTextString scoreText = new HSSFRichTextString(String.format("%.2f", questionnaireSubjectScoreVO.getAvgScore()));
                    subjectCell.setCellValue(subjectText);
                    scoreCell.setCellValue(scoreText);
                });
            }

            if(!CollectionUtils.isEmpty(secondPartScoreList)) {
                secondPartScoreList.stream().forEach(questionnaireSubjectScoreVO->{
                    int index=secondPartScoreList.indexOf(questionnaireSubjectScoreVO);
                    HSSFRow questionnaireSubjectScoreVORow = sheet.getRow(firstPartScoreList.size()+2+index)==null? sheet.createRow(firstPartScoreList.size()+2+index): sheet.getRow(firstPartScoreList.size()+2+index);
                    HSSFCell subjectCell = questionnaireSubjectScoreVORow.createCell(2);
                    HSSFCell scoreCell = questionnaireSubjectScoreVORow.createCell(3);
                    subjectCell.setCellStyle(noticeStyle);
                    scoreCell.setCellStyle(noticeStyle);
                    HSSFRichTextString subjectText = new HSSFRichTextString(questionnaireSubjectScoreVO.getQuestionnaireTop());
                    HSSFRichTextString scoreText = new HSSFRichTextString(String.format("%.2f", questionnaireSubjectScoreVO.getAvgScore()));
                    subjectCell.setCellValue(subjectText);
                    scoreCell.setCellValue(scoreText);
                });
            }
            if(!CollectionUtils.isEmpty(thirdPartScoreList)) {
                thirdPartScoreList.stream().forEach(questionnaireSubjectScoreVO->{
                    int index=thirdPartScoreList.indexOf(questionnaireSubjectScoreVO);
                    HSSFRow questionnaireSubjectScoreVORow =sheet.getRow(firstPartScoreList.size()+secondPartScoreList.size()+2+index)==null? sheet.createRow(firstPartScoreList.size()+secondPartScoreList.size()+2+index):sheet.getRow(firstPartScoreList.size()+secondPartScoreList.size()+2+index);
                    HSSFCell subjectCell = questionnaireSubjectScoreVORow.createCell(2);
                    HSSFCell scoreCell = questionnaireSubjectScoreVORow.createCell(3);
                    subjectCell.setCellStyle(noticeStyle);
                    scoreCell.setCellStyle(noticeStyle);
                    HSSFRichTextString subjectText = new HSSFRichTextString(questionnaireSubjectScoreVO.getQuestionnaireTop());
                    HSSFRichTextString scoreText = new HSSFRichTextString(String.format("%.2f", questionnaireSubjectScoreVO.getAvgScore()));
                    subjectCell.setCellValue(subjectText);
                    scoreCell.setCellValue(scoreText);
                });
            }
            sheet.addMergedRegion(new CellRangeAddress(2, firstPartScoreList.size()+1, 0, 0));
            sheet.addMergedRegion(new CellRangeAddress(2, firstPartScoreList.size()+1, 1, 1));
            sheet.addMergedRegion(new CellRangeAddress(firstPartScoreList.size()==0?3:firstPartScoreList.size()+2, secondPartScoreList.size()+firstPartScoreList.size()+1, 0, 0));
            sheet.addMergedRegion(new CellRangeAddress(firstPartScoreList.size()==0?3:firstPartScoreList.size()+2, secondPartScoreList.size()+firstPartScoreList.size()+1, 1, 1));
            sheet.addMergedRegion(new CellRangeAddress(secondPartScoreList.size()+firstPartScoreList.size()+2, thirdPartScoreList.size()+secondPartScoreList.size()+firstPartScoreList.size()+1, 0, 0));
            sheet.addMergedRegion(new CellRangeAddress(secondPartScoreList.size()+firstPartScoreList.size()+2, thirdPartScoreList.size()+secondPartScoreList.size()+firstPartScoreList.size()+1, 1, 1));

        } catch (Exception e) {
            logger.error("导出excel", e);
            e.printStackTrace();
        }
    }
}
